package iotwisp.base.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

public class ExcelUtil {

	public static Cell getCellByName(Workbook wb, String cellName) {
		//  Retrieve the named range

		//  Will be something like "$C$10,$D$12:$D$14";

		int namedCellIdx = wb.getNameIndex(cellName);

		Name aNamedCell = wb.getNameAt(namedCellIdx);

		//  Retrieve the cell at the named range and test its contents

		//  Will get back one AreaReference for C10, and

		//   another for D12 to D14

		AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getRefersToFormula());

		for (int i = 0; i < arefs.length; i++) {

			//  Only get the corners of the Area

			//  (use arefs[i].getAllReferencedCells() to get all cells)

			CellReference[] crefs = arefs[i].getAllReferencedCells();

			for (int j = 0; j < crefs.length; j++) {

				//  Check it turns into real stuff

				Sheet s = wb.getSheet(crefs[j].getSheetName());

				Row r = s.getRow(crefs[j].getRow());

				Cell cell = r.getCell(crefs[j].getCol());

				//  Do something with this corner cell
				return cell;
			}

		}
		return null;
	}

	// 读取excel
	public static Workbook readExcel(String filePath) throws IOException {
		Workbook wb = null;
		if (filePath == null) {
			return null;
		}
		String extString = filePath.substring(filePath.lastIndexOf("."));
		InputStream is = null;
		try {
			is = new FileInputStream(filePath);
			if (".xls".equals(extString)) {
				return wb = new HSSFWorkbook(is);
			} else if (".xlsx".equals(extString)) {
				return wb = new XSSFWorkbook(is);
			} else {
				return wb = null;
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			is.close();
		}
		return wb;
	}

	public static boolean isCellExist(Row row, int i) {
		if (row.getCell(i) != null && row.getCell(i).getCellTypeEnum() != CellType.BLANK) {
			return true;
		} else {
			return false;
		}
	}

	public static boolean isBooleanCellExist(Row row, int i) {
		return row.getCell(i) != null && !StringUtils.isEmpty(row.getCell(i).getBooleanCellValue());
	}

	public static boolean isRowExist(Row row, int i) {
		return row.getCell(i) != null && !StringUtils.isEmpty(row.getCell(i).getStringCellValue());
	}
}
